﻿Imports System.Data.SqlClient

Public Class <OwnerU>_<TableU>Service
    Implements I<OwnerU>_<TableU>Service

    Public Function SelectAll() As DataSet Implements I<OwnerU>_<TableU>Service.SelectAll
        Dim connection As SqlConnection = <Database>DataService.GetConnection
        connection.Open()
        Dim selectStatement As String _
            = "SELECT " _ 
            <If Loop>
               <IsRelated>
	       <And>
	       <IsMiscTag>
               <And>
               <IsCurRow=[0]>
            & "     [<FieldC>] = [A<MiscTag>].[<RDisplayField>] " _
               <IsRelated>
	       <And>
	       <IsMiscTag>
               <And>
               <IsCurRow<>[0]>
            & "    ,[<FieldC>] = [A<MiscTag>].[<RDisplayField>] " _
               <IsRelated>
	       <And>
	       <IsNotMiscTag>
               <And>
               <IsCurRow=[0]>
            & "     [<FieldC>] = [<ROwner>].[<RTable>].[<RDisplayField>] " _
               <IsRelated>
	       <And>
	       <IsNotMiscTag>
               <And>
               <IsCurRow<>[0]>
            & "    ,[<FieldC>] = [<ROwner>].[<RTable>].[<RDisplayField>] " _
               <IsNotRelated>
               <And>
               <IsCurRow=[0]>
            & "     [<FieldC>] = [<Owner>].[<Table>].[<Field>] " _
               <IsNotRelated>
               <And>
               <IsCurRow<>[0]>
            & "    ,[<FieldC>] = [<Owner>].[<Table>].[<Field>] " _
            </If Loop>
            & "FROM " _
            & "     [<Owner>].[<Table>] " _
            <If Loop>
               <IsRelated>
	       <And>
	       <IsMiscTag>
	       <And>
	       <IsNull>
            & "LEFT JOIN [<ROwner>].[<RTable>] as [A<MiscTag>] ON [<Owner>].[<Table>].[<Field>] = [A<MiscTag>].[<RField>] " _
               <IsRelated>
	       <And>
	       <IsNotMiscTag>
	       <And>
	       <IsNull>
            & "LEFT JOIN [<ROwner>].[<RTable>] ON [<Owner>].[<Table>].[<Field>] = [<ROwner>].[<RTable>].[<RField>] " _
               <IsRelated>
	       <And>
	       <IsMiscTag>
	       <And>
	       <IsNotNull>
            & "INNER JOIN [<ROwner>].[<RTable>] as [A<MiscTag>] ON [<Owner>].[<Table>].[<Field>] = [A<MiscTag>].[<RField>] " _
               <IsRelated>
	       <And>
	       <IsNotMiscTag>
	       <And>
	       <IsNotNull>
            & "INNER JOIN [<ROwner>].[<RTable>] ON [<Owner>].[<Table>].[<Field>] = [<ROwner>].[<RTable>].[<RField>] " _
            </If Loop>
            & ""
        Dim selectCommand As New SqlCommand(selectStatement, connection)
        selectCommand.CommandType = CommandType.Text
        Dim sda As New SqlDataAdapter(selectCommand)
        Dim ds As New DataSet
        sda.Fill(ds)
        selectCommand.ExecuteNonQuery()
        connection.Close()
        Return ds
    End Function

    Public Function Select_Record(ByVal cls<OwnerU>_<TableU>Para As <OwnerU>_<TableU>Class) As <OwnerU>_<TableU>Class Implements I<OwnerU>_<TableU>Service.Select_Record
        Dim cls<OwnerU>_<TableU> As New <OwnerU>_<TableU>Class
        Dim connection As SqlConnection = <Database>DataService.GetConnection
        Dim selectStatement As String _
            = "SELECT " _ 
            <Loop>
            & "    <SF[,]>[<Field>] " _
            </Loop>
            & "FROM " _
            & "     [<Owner>].[<Table>] " _
            & "WHERE " _
            <If Loop>
               <IsPKey>
            & " <SF[AND ]>[<Field>] = @<Field> " _
            </If Loop>
            <If Loop>
               <IsNotPKey>
               <And>
               <IsIdentity>
            & "    [<Field>] = @<Field> " _
            </If Loop>
            <If Loop>
               <IsNotIdentityCount>
               <And>
               <IsNotPKeyCount>
   	       <And>
               <IsNotText>
               <And>
               <IsNotNText>
               <And>
               <IsNotXML>
            & "    [<Field>] = @<Field> " _
	       <ExitLoop>
            </If Loop>
            & ""
        Dim selectCommand As New SqlCommand(selectStatement, connection)
        selectCommand.CommandType = CommandType.Text
        <If Loop>
           <IsPKey>
        selectCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>Para.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotPKey>
           <And>
           <IsIdentity>
        selectCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>Para.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotIdentityCount>
           <And>
           <IsNotPKeyCount>
   	   <And>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
        selectCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>Para.<FieldU>)
	   <ExitLoop>
        </If Loop>
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow)
            If reader.Read Then
                With cls<OwnerU>_<TableU>
                <If Loop>
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsFloat>
                    .<FieldU> = If(IsDBNull(reader("<FieldU>")), Nothing, CType(CType(reader("<FieldU>"), Double?), Decimal?))
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsReal>
                    .<FieldU> = If(IsDBNull(reader("<FieldU>")), Nothing, CType(CType(reader("<FieldU>"), Single?), Decimal?))
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsNotFloat>
                    <And>
                    <IsNotReal>
                    .<FieldU> = If(IsDBNull(reader("<Field>")), Nothing, CType(reader("<Field>"), <VBType>?))
                    <IsNull>
                    <And>
                    <IsNotNullable>
                    .<FieldU> = If(IsDBNull(reader("<Field>")), Nothing, reader("<Field>").ToString)
                    <IsNotNull>
                    .<FieldU> = <Convert>(reader("<Field>"))
                </If Loop>
                End With
            Else
                cls<OwnerU>_<TableU> = Nothing
            End If
            reader.Close()
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return cls<OwnerU>_<TableU>
    End Function

    Public Function Add(ByVal cls<OwnerU>_<TableU> As <OwnerU>_<TableU>Class) As Boolean Implements I<OwnerU>_<TableU>Service.Add
        Dim connection As SqlConnection = <Database>DataService.GetConnection
        Dim insertStatement As String _
            = "INSERT " _ 
            & "     [<Owner>].[<Table>] " _
            & "     ( " _
            <If Loop>
               <IsNotIdentity>
            & "    <SF[,]>[<Field>] " _
            </If Loop>
            & "     ) " _
            & "VALUES " _ 
            & "     ( " _
            <If Loop>
               <IsNotIdentity>
            & "    <SF[,]>@<Field> " _
            </If Loop>
            & "     ) " _
            & ""
        Dim insertCommand As New SqlCommand(insertStatement, connection)
        insertCommand.CommandType = CommandType.Text
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        insertCommand.Parameters.AddWithValue("@<FieldU>", IIf(cls<OwnerU>_<TableU>.<FieldU>.HasValue, cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        insertCommand.Parameters.AddWithValue("@<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>.<FieldU>), cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNotNull>
        insertCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>.<FieldU>)
        </If Loop>
        Try
            connection.Open()
            Dim count As Integer = insertCommand.ExecuteNonQuery()
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return True
    End Function

    Public Function Update(ByVal cls<OwnerU>_<TableU>Old As <OwnerU>_<TableU>Class, _
           ByVal cls<OwnerU>_<TableU>New As <OwnerU>_<TableU>Class) As Boolean Implements I<OwnerU>_<TableU>Service.Update
        Dim connection As SqlConnection = <Database>DataService.GetConnection
        Dim updateStatement As String _
            = "UPDATE " _  
            & "     [<Owner>].[<Table>] " _
            & "SET " _
            <If Loop>
               <IsNotIdentity>
            & "    <SF[,]>[<Field>] = @New<Field> " _
            </If Loop>
            & "WHERE " _
            <If Loop>
               <IsNotText>
               <And>
               <IsNotNText>
               <And>
               <IsNotXML>
               <And>
               <IsNotNull>
               <And>
               <IsCurRow=[0]>
            & "     [<Field>] = @Old<Field> " _
               <IsNotText>
               <And>
               <IsNotNText>
               <And>
               <IsNotXML>
               <And>
               <IsNotNull>
               <And>
               <IsCurRow<>[0]>
            & " AND [<Field>] = @Old<Field> " _
               <IsNotText>
               <And>
               <IsNotNText>
               <And>
               <IsNotXML>
               <And>
               <IsNull>
               <And>
               <IsCurRow=[0]>
            & "     ((@Old<FieldU> IS NULL AND [<Field>] IS NULL) OR [<Field>] = @Old<Field>) " _
               <IsNotText>
               <And>
               <IsNotNText>
               <And>
               <IsNotXML>
               <And>
               <IsNull>
               <And>
               <IsCurRow<>[0]>
            & " AND ((@Old<FieldU> IS NULL AND [<Field>] IS NULL) OR [<Field>] = @Old<Field>) " _
            </If Loop>
            & ""
        Dim updateCommand As New SqlCommand(updateStatement, connection)
        updateCommand.CommandType = CommandType.Text
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", IIf(cls<OwnerU>_<TableU>New.<FieldU>.HasValue, cls<OwnerU>_<TableU>New.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>New.<FieldU>), cls<OwnerU>_<TableU>New.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNotNull>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", cls<OwnerU>_<TableU>New.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", cls<OwnerU>_<TableU>Old.<FieldU>)
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(cls<OwnerU>_<TableU>Old.<FieldU>.HasValue, cls<OwnerU>_<TableU>Old.<FieldU>, DBNull.Value))
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>Old.<FieldU>), cls<OwnerU>_<TableU>Old.<FieldU>, DBNull.Value))
        </If Loop>
        Try
            connection.Open()
            Dim count As Integer = updateCommand.ExecuteNonQuery()
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return True
    End Function

    Public Function Delete(ByVal cls<OwnerU>_<TableU> As <OwnerU>_<TableU>Class) As Boolean Implements I<OwnerU>_<TableU>Service.Delete
        Dim connection As SqlConnection = <Database>DataService.GetConnection
        Dim deleteStatement As String _
            = "DELETE FROM " _  
            & "     [<Owner>].[<Table>] " _
            & "WHERE " _
            <If Loop>
               <IsNotText>
               <And>
               <IsNotNText>
               <And>
               <IsNotXML>
               <And>
               <IsNotNull>
               <And>
               <IsCurRow=[0]>
            & "     [<Field>] = @Old<Field> " _
               <IsNotText>
               <And>
               <IsNotNText>
               <And>
               <IsNotXML>
               <And>
               <IsNotNull>
               <And>
               <IsCurRow<>[0]>
            & " AND [<Field>] = @Old<Field> " _
               <IsNotText>
               <And>
               <IsNotNText>
               <And>
               <IsNotXML>
               <And>
               <IsNull>
               <And>
               <IsCurRow=[0]>
            & "     ((@Old<FieldU> IS NULL AND [<Field>] IS NULL) OR [<Field>] = @Old<Field>) " _
               <IsNotText>
               <And>
               <IsNotNText>
               <And>
               <IsNotXML>
               <And>
               <IsNull>
               <And>
               <IsCurRow<>[0]>
            & " AND ((@Old<FieldU> IS NULL AND [<Field>] IS NULL) OR [<Field>] = @Old<Field>) " _
            </If Loop>
            & ""
        Dim deleteCommand As New SqlCommand(deleteStatement, connection)
        deleteCommand.CommandType = CommandType.Text
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", cls<OwnerU>_<TableU>.<FieldU>)
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(cls<OwnerU>_<TableU>.<FieldU>.HasValue, cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>.<FieldU>), cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
        </If Loop>
        Try
            connection.Open()
            Dim count As Integer = deleteCommand.ExecuteNonQuery()
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return True
    End Function

End Class
